package com.bj58.data.emailreports.service.impl;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.BWRAllLineInc;
import com.bj58.data.emailreports.model.BWRAllOne;
import com.bj58.data.emailreports.model.BWRAllOneJL;
import com.bj58.data.emailreports.model.BWRJzFluxFunnel;
import com.bj58.data.emailreports.model.BWRLineAccount;
import com.bj58.data.emailreports.model.BWRLineCityCate;
import com.bj58.data.emailreports.model.BWRLineCusTran;
import com.bj58.data.emailreports.model.BWRLineFlux;
import com.bj58.data.emailreports.model.BWRLineJTChange;
import com.bj58.data.emailreports.model.BWRLineSPIncome;
import com.bj58.data.emailreports.model.WRConsumCycleVo;
import com.bj58.data.emailreports.model.WRCusFunnelVo;

import com.bj58.data.emailreports.model.BMRNaturalFluxData;
import com.bj58.data.emailreports.model.BMRDemandAnalysisData;
import com.bj58.data.emailreports.model.onlineProductIncome;

import com.bj58.data.emailreports.service.BusiMonthReportService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.DateUtil;
import com.bj58.data.emailreports.utils.MyStore;
import com.bj58.data.emailreports.utils.ReflectUtil;
import com.bj58.data.emailreports.utils.VelocityTools;

public class BusiMonthReportServiceImpl implements BusiMonthReportService {
	
	private CommonDataDeal comDeal = new CommonDataDeal();
	private ReflectUtil reflectUtil = new ReflectUtil();
	private VelocityTools vt = new VelocityTools();
	
	/**各业务线查询条件Map
	 * @return
	 */
	private static HashMap<String,String>  getBusiLineConsMap()throws Exception{
		HashMap<String,String> busiLineMap = new HashMap<String,String>();
		busiLineMap.put("all", " 1=1 ");
		busiLineMap.put("huangye", MyStore.hyCondition);
		busiLineMap.put("zhaopin", MyStore.zpCondtion);
		busiLineMap.put("ershou", MyStore.esCondtion);
		busiLineMap.put("ershouche", MyStore.escCondtion);
		busiLineMap.put("fangchan", MyStore.fcCondtion);
		
		return busiLineMap;
	}
	
	@Override
	public List<BWRAllOne> getBMRAllOneList(String searchDate)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		//各业务线 完成数
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT stat_date,business_type,");
		sql.append(" sum(if(income_type in ('jingzhun','zhiding'),cash,0))/1000000 'all', ");
		sql.append(" sum(if(income_type='jingzhun',cash,0))/1000000 'jz', ");
		sql.append(" sum(if(income_type='zhiding',cash,0))/1000000 'zd', ");
		sql.append(" sum(if(income_type='zhineng',cash,0))/1000000 'zn' ");
		sql.append(" FROM t_amortize_day_" + yyyyMM );
		sql.append(" where stat_date ='"+searchDate+"' ");
		sql.append(" and business_type in('huangye','zhaopin','ershou','ershouche','fangchan') ");
		sql.append(" GROUP BY business_type ");
		
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		List<String> cols1 = new ArrayList<String>();
		cols1.add("statDate");
		cols1.add("busiLine");
		cols1.add("allDone");
		cols1.add("jzDone");
		cols1.add("zdDone");
		cols1.add("znDone");
		sqlMap.put(sql.toString(), cols1);
		
		
		//总计 完成数
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT stat_date,'all',");
		sql.append(" sum(if(income_type in ('jingzhun','zhiding'),cash,0))/1000000 'all', ");
		sql.append(" sum(if(income_type='jingzhun',cash,0))/1000000 'jz', ");
		sql.append(" sum(if(income_type='zhiding',cash,0))/1000000 'zd', ");
		sql.append(" sum(if(income_type='zhineng',cash,0))/1000000 'zn' ");
		sql.append(" FROM t_amortize_day_" + yyyyMM );
		sql.append(" where stat_date ='"+searchDate+"' ");
//		sql.append(" and business_type in('huangye','zhaopin','ershou','ershouche','fangchan') ");
		
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("busiLine");
		cols2.add("allDone");
		cols2.add("jzDone");
		cols2.add("zdDone");
		cols2.add("znDone");
		sqlMap.put(sql.toString(), cols2);
		
		//KPI
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT '"+searchDate+"',busiLine, ");
		sql.append(" allKpi,jzKpi,zdKpi,znKpi ");
		sql.append(" FROM kpi_week_report ");
		sql.append(" where month ='" + yyyyMM + "' ");
		sql.append(" group by busiLine ");
		
		sql.append(" :"+ DBType.db_68_bi_sys.getValue());
		List<String> cols3 = new ArrayList<String>();
		cols3.add("statDate");
		cols3.add("busiLine");
		cols3.add("allKpi");
		cols3.add("jzKpi");
		cols3.add("zdKpi");
		cols3.add("znKpi");
		sqlMap.put(sql.toString(), cols3);
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		
		List<BWRAllOne> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRAllOne.class, gbList);
		
		//计算完成率
		for (BWRAllOne vo : rsList) {
			String busiLine = vo.getBusiLine();
			vo.setBusiLine(MyStore.busilineTran(busiLine));
			
			vo.setAllHasDone(vo.getAllDone()/vo.getAllKpi());
			
			vo.setJzHasDone(vo.getJzDone()/vo.getJzKpi());
			
			vo.setZdHasDone(vo.getZdDone()/vo.getZdKpi());
			
			if(vo.getZnKpi() > 0 ){
				vo.setZnHasDone(vo.getZnDone()/vo.getZnKpi());
			}
		}		
		return rsList;
	}
	
	@Override
	public Map<String,BWRAllOneJL> getBMRAllOneJLList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		String today = dateList.get(0);
		String yyyyMM = today.substring(0,today.lastIndexOf("-")).replace("-", "");
			
		//KPI
		sql.delete(0, sql.length()+1);
		sql.append(" SELECT '" + today + "',jlkpi*10000  ");
		sql.append(" FROM kpi_week_report " );
		sql.append(" where month ='" + yyyyMM + "' ");
		sql.append(" and busiline = 'all' ");
		
		sql.append(" :"+ DBType.db_68_bi_sys.getValue());
		List<String> cols2 = new ArrayList<String>();
		cols2.add("statDate");
		cols2.add("kpi");
		sqlMap.put(sql.toString(), cols2);
			
		//简历购买收入、客户数
		for(String searchDate : dateList){
			
			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
					
			yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			sql.delete(0, sql.length()+1);
			sql.append(" select '"+searchDate+"',");
			sql.append(" sum(a.gm)/count(distinct stat_date) as '购买收入',");
			sql.append(" sum(a.kh)/count(distinct stat_date) as '购买客户数',");
			sql.append(" sum(a.gm)/sum(a.kh) as 'ARPU' ");
			sql.append(" from( ");
			sql.append(" select stat_date,sum(resume_amount-discount_amount) gm,count(distinct user_id) kh ");
			sql.append(" from udp_buyResumePackage_detail_" + yyyyMM);
			sql.append(" where  stat_date >= '"+beginday+"'  ");
			sql.append(" and stat_date <= '"+endday+"'  ");
			sql.append(" and (resume_amount-discount_amount)>0 ");
			sql.append(" group by stat_date ");
			sql.append(" )a;");
			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
			List<String> cols3 = new ArrayList<String>();
			cols3.add("statDate");
			cols3.add("income");
			cols3.add("cusCount");
			cols3.add("ARPU");
			sqlMap.put(sql.toString(), cols3);
						
			//简历摊销本月完成数和日均摊销
			sql.delete(0, sql.length()+1);
			
			sql.append(" SELECT '" + searchDate + "', ");
			sql.append(" sum(a.tx) as '完成摊销',sum(a.tx)/count(distinct vDate) as '日均摊销' ");
			sql.append(" from( ");
			sql.append(" select vDate,sum(dayAmortize) tx ");
			sql.append(" from amortize_metrics ");
			sql.append(" where memberId in(1038,1097,1104)  ");
			sql.append(" and vDate >= '" +  beginday+ "' ");
			sql.append(" and vDate <= '"+endday+ "' ");
			sql.append(" group by vDate)a; ");
			
			sql.append(" :"+ DBType.db_131_ors.getValue());
			List<String> cols1 = new ArrayList<String>();
			cols1.add("statDate");
			cols1.add("doneAmor");
			cols1.add("dayAvgAmor");
			sqlMap.put(sql.toString(), cols1);
			
		}

		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		
		List<BWRAllOneJL> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRAllOneJL.class, gbList);
		
		Map<String,BWRAllOneJL> rsMap = new HashMap<String, BWRAllOneJL>();
		//最后计算   实际进度
		for (BWRAllOneJL vo : rsList) {
			if(vo.getStatDate().equals(today)){
				vo.setDone(vo.getDoneAmor()/vo.getKpi());
			}
			
			rsMap.put(vo.getStatDate(), vo);
		}
		
		return rsMap;
	}
	
	@Override
	public List<BWRAllLineInc> getBMRAllLineIncList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> incomeTypeList = new ArrayList<String>();
		incomeTypeList.add("jingzhun");
		incomeTypeList.add("zhiding");
		incomeTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			
			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
			
			String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			for(String incomeType : incomeTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"',");
					sql.append(" round(sum(a.xf)/ count(distinct a.stat_date),2)  as'日均消费', ");
					sql.append(" round(sum(a.xjxf)/ count(distinct a.stat_date),2)  as'现金消费',");
					sql.append(" round(sum(a.kh)/ count(distinct a.stat_date),2)  as'客户数', ");
					sql.append(" (round(sum(a.xf)/ count(distinct a.stat_date),2))/(round(sum(a.kh)/ count(distinct a.stat_date),2))  as'ARPU' ");
					sql.append(" from( ");
					sql.append(" select stat_date,sum(cost)/100 xf,sum(cash)/100 xjxf ,count(distinct if(cost>0,user_id,null)) kh ");
					
					if("zhiding".equals(incomeType)){
						sql.append(" from t_"+incomeType+"_user_stat_belong_" + yyyyMM);
					}
					else{
						sql.append(" from t_"+incomeType+"_user_stat_new_" + yyyyMM);
					}
									
					sql.append(" where stat_date >= '"+beginday+"'  ");
					sql.append(" and stat_date <= '"+endday+"'  ");
					if(!"all".equals(busiName)){
						sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					sql.append(" group by stat_date ");
					sql.append(" )a ");
										
					if("zhiding".equals(incomeType)){
						sql.append(" :"+ DBType.db_94_data_app.getValue());
					}
					else{
						sql.append(" :"+ DBType.db_94_data_app.getValue());
					}
					
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					
					if("jingzhun".equals(incomeType)){
						cols1.add("jzCost");
						cols1.add("jzCash");
						cols1.add("jzCusCount");
						cols1.add("jzARPU");
					}else if("zhiding".equals(incomeType)){
						cols1.add("zdCost");
						cols1.add("zdCash");
						cols1.add("zdCusCount");
						cols1.add("zdARPU");
					}else if("zhineng".equals(incomeType)){
						cols1.add("znCost");
						cols1.add("znCash");
						cols1.add("znCusCount");
						cols1.add("znARPU");
					}
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		
		List<BWRAllLineInc> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRAllLineInc.class, gbList);
		
		
		//计算 各业务线的总 cost/cash/cusCount/ARPU
		for (BWRAllLineInc vo : rsList) {
			vo.setCost(vo.getJzCost() + vo.getZdCost() + vo.getZnCost());
			vo.setCash(vo.getJzCash() + vo.getZdCash() + vo.getZnCash());
			vo.setCusCount(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount());
			vo.setARPU((vo.getCost()) / vo.getCusCount());
			
			vo.setBusiLine(MyStore.busilineTran(vo.getBusiLine()));
		}
		return rsList;
	}
	
	@Override
	public Map<String,List<BWRLineFlux>> getBMRAllLineFluxList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> incomeTypeList = new ArrayList<String>();
		incomeTypeList.add("jingzhun");
		incomeTypeList.add("zhiding");
		incomeTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			
			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
			
			String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			for(String incomeType : incomeTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType + "',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),2)  as'PV(W)', ");
					sql.append(" round(sum(a.adpv)/ count(distinct a.stat_date),2)  as'ADPV(W)',");
					sql.append(" round(sum(a.disp)/ count(distinct a.stat_date),2)  as'ADIMP(W)',");
					sql.append(" round(sum(a.click)/ count(distinct a.stat_date),2)  as'CLICK',");
					sql.append(" (round(sum(a.adpv)/ count(distinct a.stat_date),2)) /(round(sum(a.pv)/ count(distinct a.stat_date),2) ) as 'PVR', ");//PVR = ADPV/PV
					sql.append(" (round(sum(a.disp)/ count(distinct a.stat_date),2)) /(round(sum(a.adpv)/ count(distinct a.stat_date),2) ) as 'ASN', ");//ASN = ADIMP/ADPV
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)*1000) /(round(sum(a.disp)/ count(distinct a.stat_date),2) *10000) as 'CPM', ");//CPM = COST/ADIMP*1000
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)) /(round(sum(a.click)/ count(distinct a.stat_date),2) ) as 'ACP', ");//ACP=消费/CLICK
					sql.append(" (round(sum(a.click)/ count(distinct a.stat_date),2)) /(round(sum(a.disp)/ count(distinct a.stat_date),2) * 10000 ) as 'CTR2' ");//CTR2=CLICK/ADIMP
					
					sql.append(" from( ");
					sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
					sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
					sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
					sql.append(" where stat_date >= '"+beginday+"' ");
					sql.append(" and stat_date <= '"+endday+ "' ");
					if(!"all".equals(busiName)){
						sql.append(" and business_type_biz ='" + busiName +"' ");
					}
					sql.append(" group by stat_date ");
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_94_data_app.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					
					cols1.add("pv");
					cols1.add("adpv");
					cols1.add("adimp");
					cols1.add("click");
					cols1.add("PVR");
					cols1.add("ASN");
					cols1.add("CPM");
					cols1.add("ACP");
					cols1.add("CTR2");
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineFlux> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineFlux.class, gbList);
		
		Map<String,List<BWRLineFlux>>  rsMap = new HashMap<String, List<BWRLineFlux>>();
		for (BWRLineFlux vo : rsList) {
			vo.setIncomeType(MyStore.incomeTypeTran(vo.getIncomeType()));
			
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineFlux> tempList = new ArrayList<BWRLineFlux>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}

	@Override
	public Map<String,List<BWRLineFlux>> getBMRLinePageFluxList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> incomeTypeList = new ArrayList<String>();
		incomeTypeList.add("jingzhun");
		incomeTypeList.add("jingzhun-list");
		incomeTypeList.add("jingzhun-detail");
		incomeTypeList.add("zhiding");
		incomeTypeList.add("zhiding-list");
		incomeTypeList.add("zhiding-detail");		
		incomeTypeList.add("zhineng");
		
		for(String searchDate : dateList){
			
			String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");

			for(String incomeType : incomeTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					if("all".equals(busiName)){ //总体 PASS
						continue;
					}			
					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType + "',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),5)  as'PV(W)', ");
					sql.append(" round(sum(a.adpv)/ count(distinct a.stat_date),5)  as'ADPV(W)',");
					sql.append(" round(sum(a.disp)/ count(distinct a.stat_date),5)  as'ADIMP(W)',");
					sql.append(" round(sum(a.click)/ count(distinct a.stat_date),5)  as'CLICK',");
					sql.append(" (round(sum(a.adpv)/ count(distinct a.stat_date),2)) /(round(sum(a.pv)/ count(distinct a.stat_date),2) ) as 'PVR', ");//PVR = ADPV/PV
					sql.append(" (round(sum(a.disp)/ count(distinct a.stat_date),2)) /(round(sum(a.adpv)/ count(distinct a.stat_date),2) ) as 'ASN', ");//ASN = ADIMP/ADPV
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)*1000) /(round(sum(a.disp)/ count(distinct a.stat_date),2) *10000) as 'CPM', ");//CPM = COST/ADIMP*1000
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)) /(round(sum(a.click)/ count(distinct a.stat_date),2) ) as 'ACP', ");//ACP=消费/CLICK
					sql.append(" (round(sum(a.click)/ count(distinct a.stat_date),2)) /(round(sum(a.disp)/ count(distinct a.stat_date),2) * 10000 ) as 'CTR2' ");//CTR2=CLICK/ADIMP
					
					sql.append(" from( ");
					sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
					sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
					if(incomeType.contains("jingzhun"))
					{
						sql.append(" from t_jingzhun_source_cate_pagetype_"  + yyyyMM);
					}else if(incomeType.contains("zhiding"))
					{
						sql.append(" from t_zhiding_source_cate_pagetype_"  + yyyyMM);
					}else
					{
						sql.append(" from t_zhineng_city_cate_stat_new_"  + yyyyMM);
					}
					sql.append(" where business_type_biz ='" + busiName +"' ");	
					if(incomeType.contains("list"))
					{
						sql.append(" and page_type=1");
					}else if(incomeType.contains("detail"))
					{
						sql.append(" and page_type=2");
					}
					sql.append(" group by stat_date ");
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_131_data_bi.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					
					cols1.add("pv");
					cols1.add("adpv");
					cols1.add("adimp");
					cols1.add("click");
					cols1.add("PVR");
					cols1.add("ASN");
					cols1.add("CPM");
					cols1.add("ACP");
					cols1.add("CTR2");
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineFlux> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineFlux.class, gbList);
		
		Map<String,List<BWRLineFlux>>  rsMap = new HashMap<String, List<BWRLineFlux>>();
		for (BWRLineFlux vo : rsList) {
			vo.setIncomeType(MyStore.incomeTypeTran2(vo.getIncomeType()));
			
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineFlux> tempList = new ArrayList<BWRLineFlux>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}
		
		return rsMap;
	}

	@Override
	public Map<String,List<BWRLineFlux>> getBMRLineJingzhunFluxList(List<String> dateList)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		List<String> platformTypeList = new ArrayList<String>();
		platformTypeList.add("PC");
		platformTypeList.add("M");
		platformTypeList.add("APP");
		
		for(String searchDate : dateList){
			
			String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			for(String platformType : platformTypeList){
				
				for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
					String busiName = it.next();
					
					if("all".equals(busiName)){ //总体 PASS
						continue;
					}				
					sql.delete(0, sql.length()+1);
					
					sql.append(" select '" + searchDate + "','"+busiName+"','" + platformType + "',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),5)  as'PV(W)', ");
					sql.append(" round(sum(a.adpv)/ count(distinct a.stat_date),5)  as'ADPV(W)',");
					sql.append(" round(sum(a.disp)/ count(distinct a.stat_date),5)  as'ADIMP(W)',");
					sql.append(" round(sum(a.click)/ count(distinct a.stat_date),5)  as'CLICK',");
					sql.append(" (round(sum(a.adpv)/ count(distinct a.stat_date),2)) /(round(sum(a.pv)/ count(distinct a.stat_date),2) ) as 'PVR', ");//PVR = ADPV/PV
					sql.append(" (round(sum(a.disp)/ count(distinct a.stat_date),2)) /(round(sum(a.adpv)/ count(distinct a.stat_date),2) ) as 'ASN', ");//ASN = ADIMP/ADPV
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)*1000) /(round(sum(a.disp)/ count(distinct a.stat_date),2) *10000) as 'CPM', ");//CPM = COST/ADIMP*1000
					sql.append(" (round(sum(a.cost)/ count(distinct a.stat_date),2)) /(round(sum(a.click)/ count(distinct a.stat_date),2) ) as 'ACP', ");//ACP=消费/CLICK
					sql.append(" (round(sum(a.click)/ count(distinct a.stat_date),2)) /(round(sum(a.disp)/ count(distinct a.stat_date),2) * 10000 ) as 'CTR2' ");//CTR2=CLICK/ADIMP
					
					sql.append(" from( ");
					sql.append(" select stat_date,sum(pv)/10000 pv,sum(adpv)/10000 adpv , ");
					sql.append(" sum(disp)/10000 disp,sum(click) click,sum(cost/100) cost  ");
					sql.append(" from t_jingzhun_source_cate_pagetype_" + yyyyMM);
					sql.append(" where business_type_biz ='" + busiName +"' ");	
					if(platformType.equals("PC"))
					{
						sql.append(" and platform=-1");
						
					}else if(platformType.equals("M"))
					{
						sql.append(" and platform=0");
					}else
					{
						sql.append(" and platform=2");
					}
					sql.append(" group by stat_date ");
					sql.append(" )a ");
					
					sql.append(" :"+ DBType.db_131_data_bi.getValue());
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("busiLine");
					cols1.add("incomeType");
					
					cols1.add("pv");
					cols1.add("adpv");
					cols1.add("adimp");
					cols1.add("click");
					cols1.add("PVR");
					cols1.add("ASN");
					cols1.add("CPM");
					cols1.add("ACP");
					cols1.add("CTR2");
					
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
		gbList.add("incomeType");
		
		List<BWRLineFlux> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineFlux.class, gbList);
		
		Map<String,List<BWRLineFlux>>  rsMap = new HashMap<String, List<BWRLineFlux>>();
		for (BWRLineFlux vo : rsList) {
			String busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<BWRLineFlux> tempList = new ArrayList<BWRLineFlux>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}	
			tempList.add(vo);
			rsMap.put(busiLine, tempList);
		}		
		return rsMap;
	}

	@Override
	public List<WRCusFunnelVo> cusBanlanceDatas(List<String> dateList,String channel)
			throws Exception {
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		
		StringBuffer sql = new StringBuffer();
		
		HashMap<String, String> busLineConditionMap = MyStore.getBusLineConditionMap();
		
		List<String> typeList = new ArrayList<String>();
		typeList.add("总体");
		typeList.add("会员");
		typeList.add("非会员");
		for (String searchDate : dateList) {
			String month = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			for (String  type : typeList) {
				
				String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
				String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
				
				//余额总客户数   余额大于50客户数 现金余额>0客户数 现金余额>50客户数
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");cols1.add("type");
				cols1.add("allCus");cols1.add("left0");cols1.add("left50");
				cols1.add("cashLeft0");cols1.add("cashLeft50");
				sql.delete(0, sql.length()+1);
				sql.append(" SELECT  '"+searchDate+"',");
				//infobright 中文问题哎....字符占位 后期处理
				if("总体".equals(type)){
					sql.append(" 'A',COUNT(DISTINCT user_id) AS '余额客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance + adgift_balance > 0 THEN user_id END) AS '余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN (adcash_balance + adgift_balance)/100 > 50 THEN user_id END) AS '余额>50客户数',  ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance > 0 THEN user_id END) AS '现金余额>0客户数',  ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance/100 > 50 THEN user_id END) AS '现金余额>50客户数'   ");
				}else if("会员".equals(type)){
					sql.append(" 'B',COUNT(DISTINCT CASE WHEN is_vip = 1 then user_id end) AS '会员余额客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance + adgift_balance > 0 and is_vip = 1 THEN user_id END) AS '会员余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN (adcash_balance + adgift_balance)/100 > 50 and is_vip = 1 THEN user_id END) AS '会员余额>50客户数' , ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance > 0 and is_vip = 1 THEN user_id END) AS '会员现金余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance/100 > 50 and is_vip = 1 THEN user_id END) AS '会员现金余额>50客户数'  ");
					
				}else if("非会员".equals(type)){
					sql.append(" 'C',COUNT(DISTINCT CASE WHEN (is_vip = 0 or is_vip is null) then user_id end) AS '非会员余额客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance + adgift_balance > 0 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN (adcash_balance + adgift_balance)/100 > 50 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员余额>50客户数',  ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance  > 0 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员现金余额>0客户数', ");
					sql.append(" COUNT(DISTINCT CASE WHEN adcash_balance/100  > 50 and (is_vip = 0 or is_vip is null) THEN user_id END) AS '非会员现金余额>50客户数'  ");
				}
				sql.append(" FROM t_common_user_account_new_" + month);
				sql.append(" WHERE stat_date = '"+endday+"'  ");
				
				if(busLineConditionMap.containsKey(channel)){
					sql.append(" and " + busLineConditionMap.get(channel).replace("disp_cate1_name", "source_cate1_name").replace("disp_cate2_name", "source_cate2_name"));
				}
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				sqlMap.put(sql.toString(), cols1);
				
				
				//精准 置顶 智能 客户数
				
				String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
				
				List<String>  incomeTypeList = new ArrayList<String>();
				incomeTypeList.add("jingzhun");
				incomeTypeList.add("zhiding");
				incomeTypeList.add("zhineng");
				for (String incomeType : incomeTypeList) {
					List<String> cols2 = new ArrayList<String>();
					cols2.add("statDate");cols2.add("type");
					
					if("jingzhun".equals(incomeType)){
						cols2.add("preCus");
					}else if("zhiding".equals(incomeType)){
						cols2.add("topCus");
					}else if("zhineng".equals(incomeType)){
						cols2.add("idCus");
					}
					
					StringBuffer conditions = new StringBuffer();
					conditions.append(" where stat_date  <='"+endday+"'  and stat_date >='"+beginday+"'  ");
					
					if(busLineConditionMap.containsKey(channel)){
						conditions.append(" and " + busLineConditionMap.get(channel));
					}else{
						System.out.println(" channel " + channel +" can not find in busLineConditionMap...1");
					}
					
					
					sql.delete(0, sql.length()+1);
					sql.append(" SELECT  '"+searchDate+"',");
					if("总体".equals(type)){
						sql.append(" 'A', ");
					}else if("会员".equals(type)){
						sql.append(" 'B', ");
					}else if("非会员".equals(type)){
						sql.append(" 'C', ");
					}
					sql.append(" sum(kh)/count(distinct stat_date) from ( ");
					
					sql.append("SELECT  stat_date,");
					if("总体".equals(type)){
						sql.append("COUNT(DISTINCT user_id)  kh");
					}else if("会员".equals(type)){
						sql.append("COUNT(DISTINCT case when is_vip=1 then user_id end)  kh");
					}else if("非会员".equals(type)){
						sql.append("COUNT(DISTINCT case when is_vip=0 then user_id end)  kh");
					}
					sql.append(" from t_"+incomeType+"_user_stat_new_" + yyyyMM);
					sql.append(" " + conditions.toString() );
					sql.append(" GROUP BY stat_date ");
					sql.append(") a");
					
					sql.append("  :"+DBType.db_94_data_app.getValue());
					
					sqlMap.put(sql.toString(), cols2);
					
					
				}
				
				
			}
		}
		
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<WRCusFunnelVo> tempList =  comDeal.AssembledDataBySqlMap(sqlMap, WRCusFunnelVo.class, gbList);
		//计算精准 置顶 智能 客户占比数
		for (WRCusFunnelVo vo : tempList) {
			String type = vo.getType();
			if("A".equals(type)){
				vo.setType("总体");
			}else if("B".equals(type)){
				vo.setType("会员");
			}else if("C".equals(type)){
				vo.setType("非会员");
			}
			if(vo.getAllCus() > 0 ){
				vo.setPreRate((double)vo.getPreCus()/vo.getAllCus());
				vo.setTopRate((double)vo.getTopCus()/vo.getAllCus());
				vo.setIdRate((double)vo.getIdCus()/vo.getAllCus());
			}
		}
		return tempList;
	}

@SuppressWarnings({ "static-access", "rawtypes" })
@Override
	public List<String> consumCycleData(String today, String monthfirstday,
		String channel, String cate1, String cate2) throws Exception {
	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	
	String month = today.substring(0,today.lastIndexOf("-")).replace("-", "");
	
	StringBuffer sql = new StringBuffer();
	
	List<String> cols1 = new ArrayList<String>();  //余额
	cols1.add("city");
	cols1.add("cashAccountTotal");
	
	List<String> cols2 = new ArrayList<String>();  //消耗
	cols2.add("city");
	cols2.add("dayConsumTotal");
	if(!"".equals(cate2)){
		for(int i = 1 ; i <= cate2.split(",").length ; i++){
			cols1.add("cashAccount" + i);
			cols2.add("dayConsum" + i);
		}
	}
	
	/**获取昨天所有账户余额   以及 消耗 
	注：1.二手车的查询类别是  cate2 = '二手车'
	      2.二手车、招聘 只需要查询总体数据
	      3.房产的品类数据 需用like 租房、二手房 进行CASE WHEN SUM
	*/
	
	//全国余额
	sql.delete(0, sql.length()+1);
	sql.append(" select 'all', sum(adcash_balance)/1000000 ");
	if(!"".equals(cate2)){
		for(String cate : cate2.split(",")){
			if(cate.contains("%")){
				sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_balance end)/1000000 ");
			}else{
				sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_balance end)/1000000 ");
			}
		}
	}
	sql.append(" from t_common_user_account_new_" + month );
	sql.append(" where stat_date = '"+today+"'");
	sql.append(" and is_vip=1 ");
	sql.append(" and " + cate1);
	sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
	sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
	sql.append(" union ");
	//分城市余额
	sql.append(" select source_city1_name, sum(adcash_balance)/1000000 ");
	if(!"".equals(cate2)){
		for(String cate : cate2.split(",")){
			if(cate.contains("%")){
				sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_balance end)/1000000 ");
			}else{
				sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_balance end)/1000000 ");
			}
		}
	}
	sql.append(" from t_common_user_account_new_" + month );
	sql.append(" where stat_date = '"+today+"'");
	sql.append(" and is_vip=1");
	sql.append(" and " + cate1);
	sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
	sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
	sql.append(" group by source_city1_name ");
	sql.append(" :"+ DBType.db_94_data_app.getValue());
	sqlMap.put(sql.toString(),cols1);
	
	
	//消耗
	
		String costMonth = today.substring(0,today.lastIndexOf("-")).replace("-", "");
		sql.delete(0, sql.length()+1);
		sql.append(" select 'all', sum(adcash_consume)/100/COUNT(DISTINCT stat_date) ");
		if(!"".equals(cate2)){
			for(String cate : cate2.split(",")){
				if(cate.contains("%")){
					sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_consume/100 end)/COUNT(DISTINCT stat_date) ");
				}else{
					sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_consume/100 end)/COUNT(DISTINCT stat_date) ");
				}
			}
		}
		sql.append(" from t_common_user_account_new_" + costMonth);
		sql.append(" where stat_date >= '"+monthfirstday+"' and stat_date <= '"+today+"' ");
		sql.append(" and is_vip=1 ");
		sql.append(" and " + cate1);
		sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
		sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
		sql.append(" union ");
		sql.append(" select source_city1_name, sum(adcash_consume)/100/COUNT(DISTINCT stat_date) ");
		if(!"".equals(cate2)){
			for(String cate : cate2.split(",")){
				if(cate.contains("%")){
					sql.append(" ,sum(case when source_cate2_name like '"+ cate +"' then adcash_consume/100 end)/COUNT(DISTINCT stat_date) ");
				}else{
					sql.append(" ,sum(case when source_cate2_name = '"+ cate +"' then adcash_consume/100 end)/COUNT(DISTINCT stat_date) ");
				}
			}
		}
		sql.append(" from t_common_user_account_new_" + costMonth);
		sql.append(" where stat_date >= '"+monthfirstday+"' and stat_date <= '"+today+"' ");
		sql.append(" and is_vip=1");
		sql.append(" and " + cate1);
		sql.append(" and source_city1_name in (" + MyStore.zyCitys + ") ");
		sql.append(" and user_id not in ( select user_id from bigcustomer_id )  ");
		sql.append(" group by source_city1_name ");
		sql.append(" :"+ DBType.db_94_data_app.getValue());
		
		sqlMap.put(sql.toString(),cols2);

	
	List<String> gbList = new ArrayList<String>();
	gbList.add("city");
	
	List<WRConsumCycleVo> tempList = comDeal.AssembledDataBySqlMap(sqlMap, WRConsumCycleVo.class, gbList);
	
	
	List<String> finalList = new ArrayList<String>();
	Class clazz = WRConsumCycleVo.class;
	DecimalFormat df = new DecimalFormat("0.00");
	for (WRConsumCycleVo vo : tempList) {
		StringBuffer value = new StringBuffer();
		if("all".equals(vo.getCity())){
			vo.setCity("全国");
		}
		value.append(vo.getCity() + ";");
		
		//总计数据
		double accountTotal =  vo.getCashAccountTotal();
		double consumTotal =  vo.getDayConsumTotal();
		int costDaysTotal = (int)(accountTotal*10000 / consumTotal);
		value.append(df.format(accountTotal) +";");
		value.append(df.format(consumTotal) +";");
		value.append(costDaysTotal +";");
		//二级类数据
		if(!"".equals(cate2)){
			for(int i = 1 ; i <= cate2.split(",").length ; i++){
				String accountName = "cashAccount" + i;
				String consumName = "dayConsum" + i;
				double account = Double.parseDouble(reflectUtil.getGetterMethod(clazz, clazz.getDeclaredField(accountName)).invoke(vo).toString());
				double consum = Double.parseDouble(reflectUtil.getGetterMethod(clazz, clazz.getDeclaredField(consumName)).invoke(vo).toString());
				String costDays = "--";
				if( consum > 0){
					costDays = String.valueOf((int)(account*10000 / consum));
				}
				value.append(df.format(account) +";");
				value.append(df.format(consum) +";");
				value.append(costDays +";");
			}
		}
		
		finalList.add(value.toString());
	}
	return finalList;
}

@Override
	public Map<String, List<BWRLineCityCate>> cityDatas(List<String> dateList)
		throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	List<String> incomTypeList = new ArrayList<String>();
	incomTypeList.add("jingzhun");
	incomTypeList.add("zhiding");
	incomTypeList.add("zhineng");
	
	for(String searchDate : dateList){
		
		String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
		String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			if("all".equals(busiName)){ //总体不看
				continue;
			}
		
			//精准 置顶 智能 收入数据
			for(String incomeType : incomTypeList){
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" disp_city1_name,avg(a) as '消耗', avg(u) as '客户数' ");
				sql.append(" from (");
				sql.append(" select stat_date, disp_city1_name, sum(cash)/100 a,  ");
				sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
				sql.append(" from t_"+incomeType+"_user_stat_new_"  + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
				sql.append(" group by 1,2 ");
				sql.append(" ) a");
				sql.append(" GROUP BY disp_city1_name ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				
				if("jingzhun".equals(incomeType)){
					cols1.add("jzCash");
					cols1.add("jzCusCount");
				}else if("zhiding".equals(incomeType)){
					cols1.add("zdCash");
					cols1.add("zdCusCount");
				}else if("zhineng".equals(incomeType)){
					cols1.add("znCash");
					cols1.add("znCusCount");
				}
				
				sqlMap.put(sql.toString(), cols1);
			}
			
			//城市三端总PV
			sql.delete(0, sql.length()+1);
			sql.append(" select '" + searchDate + "','"+busiName+"',");
			sql.append(" disp_city1_name,sum(a.pv)/count(distinct stat_date) as '总PV' ");
			sql.append(" from (");
			//PC
			sql.append(" select stat_date,disp_city1_name,sum(pv) as pv");
			sql.append(" from t_netflow_pc_city_cate_pv_"  + yyyyMM);
			sql.append(" where stat_date >= '"+beginday+ "' ");
			sql.append(" and stat_date <= '"+endday+ "' ");
			sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
			sql.append(" and business_type='"+busiName+"' ");
			sql.append(" and page_type='all' ");
			if("zhaopin".equals(busiName)){
				sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
				sql.append(" and disp_cate2_name='all' ");
			}else{
				sql.append(" and disp_cate1_name='all'  ");
			}
			sql.append(" group by stat_date,disp_city1_name ");

			//M
			sql.append(" union all ");
			sql.append(" select stat_date,disp_city1_name,sum(pv) as pv ");
			sql.append(" from t_netflow_m_city_cate_pv_"  + yyyyMM);
			sql.append(" where stat_date >= '"+beginday+ "' ");
			sql.append(" and stat_date <= '"+endday+ "' ");
			sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
			
			sql.append(" and business_type='"+busiName+"' ");
			sql.append(" and page_type='all' ");
			if("zhaopin".equals(busiName)){
				sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
				sql.append(" and disp_cate2_name='all' ");
			}else{
				sql.append(" and disp_cate1_name='all'  ");
			}
			
			sql.append(" group by stat_date,disp_city1_name ");
			
			//APP
			sql.append(" union all ");
			sql.append(" select stat_date,disp_city1_name,sum(pv) as pv ");
			sql.append(" from t_netflow_app_pv_" + yyyyMM);
			sql.append(" where stat_date >= '"+beginday+ "' ");
			sql.append(" and stat_date <= '"+endday+ "' ");
			sql.append(" and disp_city1_name in (" + MyStore.zyCitys + ") ");
			
			sql.append(" and business_type='"+busiName+"' ");
			sql.append(" and page_type='all' and disp_city2_name='all' ");
			if("zhaopin".equals(busiName)){
				sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
				sql.append(" and disp_cate2_name='all'  ");
			}else{
				sql.append(" and disp_cate1_name='all'  ");
			}
			
			
			sql.append(" group by stat_date,disp_city1_name ");
			sql.append(" )a ");
			sql.append(" group by disp_city1_name ");
			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
			List<String> cols2 = new ArrayList<String>();
			cols2.add("statDate");
			cols2.add("busiLine");
			cols2.add("type");
			cols2.add("allPv");
			sqlMap.put(sql.toString(), cols2);
		}
	
		
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("statDate");
	gbList.add("busiLine");
	gbList.add("type");
	
	List<BWRLineCityCate> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCityCate.class, gbList);
	Map<String,List<BWRLineCityCate>>  rsMap = new HashMap<String, List<BWRLineCityCate>>();
	for (BWRLineCityCate vo : rsList) {
		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		List<BWRLineCityCate> tempList = new ArrayList<BWRLineCityCate>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		//计算 流量变现率 和 客户充足度     
		//流量变现率 =  精准置顶智能现金SUM/城市总PV
		//客户充足度 =  精准置顶智能客户数SUM/城市总PV
		
		vo.setFluxTranRate((double)(vo.getJzCash() + vo.getZdCash() + vo.getZnCash()) / vo.getAllPv()) ;
		vo.setCusAdequacy((double)(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount()) / vo.getAllPv()) ;
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
	}
	return rsMap;

}

@Override
	public Map<String, List<BWRLineCityCate>> cateDatas(List<String> dateList)
		throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	List<String> incomTypeList = new ArrayList<String>();
	incomTypeList.add("jingzhun");
	incomTypeList.add("zhiding");
	incomTypeList.add("zhineng");
	
	for(String searchDate : dateList){
		
		String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
		String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			if("all".equals(busiName) || "ershouche".equals(busiName)){ //总体 、二手车不看
				continue;
			}
		
			//精准 置顶 智能 收入数据
			for(String incomeType : incomTypeList){
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" disp_cate2_name,avg(a) as '消耗', avg(u) as '客户数' ");
				sql.append(" from (");
				sql.append(" select stat_date, disp_cate2_name, sum(cash)/100 a,  ");
				sql.append(" count(distinct case when cost>0 then  user_id end) u  ");
				sql.append(" from t_"+incomeType+"_user_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");
				sql.append(" and cost > 0");
				
				if("huangye".equals(busiName)){
					sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
				}else if("zhaopin".equals(busiName)){
					sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
					sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
				}else if("ershou".equals(busiName)){
					sql.append(" and disp_cate2_name in ('二手手机','宠物狗','宠物猫','二手家具','二手摩托车','台式机/配件','笔记本电脑','自行车/电动车','手机号码','办公用品/设备','二手设备','二手家电','服装/鞋帽/箱包','文体/户外/乐器','数码产品','平板电脑','成人用品') ");
				}else if("fangchan".equals(busiName)){
					sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
				}
					
				sql.append(" group by 1,2 ");
				sql.append(" ) a");
				sql.append(" GROUP BY disp_cate2_name ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				
				if("jingzhun".equals(incomeType)){
					cols1.add("jzCash");
					cols1.add("jzCusCount");
				}else if("zhiding".equals(incomeType)){
					cols1.add("zdCash");
					cols1.add("zdCusCount");
				}else if("zhineng".equals(incomeType)){
					cols1.add("znCash");
					cols1.add("znCusCount");
				}
				
				sqlMap.put(sql.toString(), cols1);
			}
			
			//城市三端总PV
			sql.delete(0, sql.length()+1);
			sql.append(" select '" + searchDate + "','"+busiName+"',");
			sql.append(" disp_cate2_name,sum(a.pv)/count(distinct stat_date) as '总PV' ");
			sql.append(" from (");
			//PC
			sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv");
			sql.append(" from t_netflow_pc_city_cate_pv_" + yyyyMM);
			sql.append(" where stat_date >= '"+beginday+ "' ");
			sql.append(" and stat_date <= '"+endday+ "' ");
			sql.append(" and pv > 0");
			
			if("huangye".equals(busiName)){
				sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
			}else if("zhaopin".equals(busiName)){
				sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
				sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
			}else if("ershou".equals(busiName)){
				sql.append(" and disp_cate2_name in ('二手手机','宠物狗','宠物猫','二手家具','二手摩托车','台式机/配件','笔记本电脑','自行车/电动车','手机号码','办公用品/设备','二手设备','二手家电','服装/鞋帽/箱包','文体/户外/乐器','数码产品','平板电脑','成人用品') ");
			}else if("fangchan".equals(busiName)){
				sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
			}
			
			
			sql.append(" and business_type='"+busiName+"' ");
			sql.append(" and page_type='all' ");
			sql.append(" and disp_city1_name='all'  ");
			sql.append(" group by stat_date,disp_cate2_name ");
	
			//M
			sql.append(" union all ");
			sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv ");
			sql.append(" from t_netflow_m_city_cate_pv_" + yyyyMM);
			sql.append(" where stat_date >= '"+beginday+ "' ");
			sql.append(" and stat_date <= '"+endday+ "' ");
			sql.append(" and pv > 0");
			if("huangye".equals(busiName)){
				sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
			}else if("zhaopin".equals(busiName)){
				sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
				sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
			}else if("ershou".equals(busiName)){
				sql.append(" and disp_cate2_name in ('二手手机','宠物狗','宠物猫','二手家具','二手摩托车','台式机/配件','笔记本电脑','自行车/电动车','手机号码','办公用品/设备','二手设备','二手家电','服装/鞋帽/箱包','文体/户外/乐器','数码产品','平板电脑','成人用品') ");
			}else if("fangchan".equals(busiName)){
				sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
			}
			sql.append(" and business_type='"+busiName+"' ");
			sql.append(" and page_type='all' ");
			sql.append(" and disp_city1_name='all'  ");
			sql.append(" group by stat_date,disp_cate2_name ");
			
			//APP
			sql.append(" union all ");
			sql.append(" select stat_date,disp_cate2_name,sum(pv) as pv ");
			sql.append(" from t_netflow_app_pv_" + yyyyMM);
			sql.append(" where stat_date >= '"+beginday+ "' ");
			sql.append(" and stat_date <= '"+endday+ "' ");
			sql.append(" and pv > 0");
			if("huangye".equals(busiName)){
				sql.append(" and disp_cate2_name in ('搬家','租车','工商注册','投资担保','家装服务','家电维修','保洁清洗','保姆/月嫂','艺术培训','管道疏通','婚庆','餐饮加盟','开锁/换锁/修锁','电脑维修','房屋维修/防水','货运物流','驾校','建材','工装服务','职业技能培训','二手回收') ");
			}else if("zhaopin".equals(busiName)){
				sql.append(" and disp_cate1_name in('全职招聘','兼职招聘')");
				sql.append(" and disp_cate2_name  in ('销售','普工/技工','餐饮','人事/行政/后勤','客服','司机/交通服务','物流/仓储','家政保洁/安保','美容/美发','淘宝职位','学生兼职','计算机/互联网/通信','娱乐/休闲','保健按摩','美术/设计/创意','房产中介','超市/百货/零售','教育培训') ");
			}else if("ershou".equals(busiName)){
				sql.append(" and disp_cate2_name in ('二手手机','宠物狗','宠物猫','二手家具','二手摩托车','台式机/配件','笔记本电脑','自行车/电动车','手机号码','办公用品/设备','二手设备','二手家电','服装/鞋帽/箱包','文体/户外/乐器','数码产品','平板电脑','成人用品') ");
			}else if("fangchan".equals(busiName)){
				sql.append(" and disp_cate2_name in ('求租房','二手房','写字楼','商铺租售/生意转让','厂房/仓库/土地/车位','租房','短租房/日租房','合租房') ");
			}
			sql.append(" and business_type='"+busiName+"' ");
			sql.append(" and page_type='all' ");
			sql.append(" and disp_city1_name='all' and disp_cate3_name='all'");
			sql.append(" group by stat_date,disp_cate2_name ");			
			sql.append(" )a ");
			sql.append(" group by disp_cate2_name ");
			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
			List<String> cols2 = new ArrayList<String>();
			cols2.add("statDate");
			cols2.add("busiLine");
			cols2.add("type");
			cols2.add("allPv");
			sqlMap.put(sql.toString(), cols2);
		}
		
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("statDate");
	gbList.add("busiLine");
	gbList.add("type");
	
	List<BWRLineCityCate> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCityCate.class, gbList);
	Map<String,List<BWRLineCityCate>>  rsMap = new HashMap<String, List<BWRLineCityCate>>();
	for (BWRLineCityCate vo : rsList) {
		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		List<BWRLineCityCate> tempList = new ArrayList<BWRLineCityCate>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		//计算 流量变现率 和 客户充足度     
		//流量变现率 =  精准置顶智能现金SUM/城市总PV
		//客户充足度 =  精准置顶智能客户数SUM/城市总PV
		if(vo.getAllPv() == 0 ){
			System.out.println(vo.getType());
		}
		vo.setFluxTranRate((double)(vo.getJzCash() + vo.getZdCash() + vo.getZnCash()) / vo.getAllPv()) ;
		vo.setCusAdequacy((double)(vo.getJzCusCount() + vo.getZdCusCount() + vo.getZnCusCount()) / vo.getAllPv()) ;
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
	}
	return rsMap;


}
@Override
	public Map<String, List<BWRLineSPIncome>> linePlatformIncomeDatas(
		List<String> dateList) throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	List<String> incomTypeList = new ArrayList<String>();
	incomTypeList.add("jingzhun");
	incomTypeList.add("zhiding");
	
	for(String searchDate : dateList){
		
		String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
		String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			if("all".equals(busiName)){ //总体 PASS
				continue;
			}
			
			for(String incomeType : incomTypeList){
				//分开
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" platform,round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
				sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
				sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
				sql.append(" from (");
				sql.append(" select stat_date,platform,sum(pv) as pv , sum(cash/100) as cash ,SUM(click) as click ,SUM(disp) as disp ");
				sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and platform in(-1,0,2) ");
				sql.append(" group by stat_date,platform  ");
				sql.append(" ) a");
				sql.append(" GROUP BY platform ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				
				if("jingzhun".equals(incomeType)){
					cols1.add("jzPv");
					cols1.add("jzCash");
					cols1.add("jzTranRate");
				}else if("zhiding".equals(incomeType)){
					cols1.add("zdPv");
					cols1.add("zdCash");
					cols1.add("zdTranRate");
				}
				
				sqlMap.put(sql.toString(), cols1);
				
				//总计 - 用于计算占比
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" 'all',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
				sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
				sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash ,SUM(click) as click ,SUM(disp) as disp ");
				sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and platform in(-1,0,2) ");
				sql.append(" group by stat_date ");
				sql.append(" ) a");
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				sqlMap.put(sql.toString(), cols1);
				
			}
			
		}
		
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("statDate");
	gbList.add("busiLine");
	gbList.add("type");
	
	List<BWRLineSPIncome> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineSPIncome.class, gbList);
	Map<String,List<BWRLineSPIncome>>  rsMap = new HashMap<String, List<BWRLineSPIncome>>();
	
	//总计MAP,不放入返回结果集仅用于计算占比
	Map<String,BWRLineSPIncome> allCountMap = new HashMap<String, BWRLineSPIncome>();
	
	for (BWRLineSPIncome vo : rsList) {
		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		String platform = vo.getType();
		if("-1".equals(platform)){
			vo.setType("PC");
		}else if("0".equals(platform)){
			vo.setType("M");
		}else if("2".equals(platform)){
			vo.setType("APP");
		}else if("all".equals(platform)){//all 总计VO只放入allCountMap
			allCountMap.put(vo.getStatDate()+vo.getBusiLine(), vo);
			continue;
		}
		
		List<BWRLineSPIncome> tempList = new ArrayList<BWRLineSPIncome>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
	}
	
	//计算占比
	for(Iterator<String> it = rsMap.keySet().iterator();it.hasNext();){
		String busiLine = it.next();
		List<BWRLineSPIncome> tempList = rsMap.get(busiLine);
		for (BWRLineSPIncome vo : tempList) {
			String key = vo.getStatDate()+vo.getBusiLine();
			if(allCountMap.containsKey(key)){
				BWRLineSPIncome allVo = allCountMap.get(key);
				vo.setJzPvZb((double) vo.getJzPv() / allVo.getJzPv());
				vo.setJzCashZb((double) vo.getJzCash() / allVo.getJzCash());
			
				vo.setZdPvZb((double) vo.getZdPv() / allVo.getZdPv());
				vo.setZdCashZb((double) vo.getZdCash() / allVo.getZdCash());
			}
		}
	}
	return rsMap;

}

@Override
	public Map<String, List<BWRLineSPIncome>> lineSourceIncomeDatas(
		List<String> dateList) throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	List<String> incomTypeList = new ArrayList<String>();
	incomTypeList.add("jingzhun");
	incomTypeList.add("zhiding");
	
	for(String searchDate : dateList){
		
		String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
		String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			if("all".equals(busiName)){ //总体 PASS
				continue;
			}
			
			for(String incomeType : incomTypeList){
				//站内和站外免费
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" 'zhannei',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
				sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
				sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
				sql.append(" from (");
				sql.append(" select stat_date,source1,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp  ");		
				sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");			
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and  source1 in('站内','直接访问')  ");
				sql.append(" group by stat_date ");
				sql.append(" ) a");
	//			sql.append(" GROUP BY source1 ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				
				if("jingzhun".equals(incomeType)){
					cols1.add("jzPv");
					cols1.add("jzCash");
					cols1.add("jzTranRate");
				}else if("zhiding".equals(incomeType)){
					cols1.add("zdPv");
					cols1.add("zdCash");
					cols1.add("zdTranRate");
				}
				
				sqlMap.put(sql.toString(), cols1);
				
				
				//站外付费
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" 'zwff',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
				sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
				sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp  ");
				sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");				
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and  source1 in('付费浏览器','广告系列','58联盟','付费其他','SEM')   ");
				sql.append(" group by stat_date  ");
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				sqlMap.put(sql.toString(), cols1);
				
				//站外其他
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" 'zwqt',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
				sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
				sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp  ");
				sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");				
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and  source1 in('站外其他','SEO','站外引荐','sou','other','其他')   ");
				sql.append(" group by stat_date  ");
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				sqlMap.put(sql.toString(), cols1);
				
				//总计 - 用于计算占比
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" 'all',round(sum(pv)/(count(distinct stat_date)),2) as 'pv', ");
				sql.append(" round(sum(cash)/(count(distinct stat_date)),2) as '收入', ");
				sql.append(" sum(click)/sum(disp) AS 'ctr2' ");
				sql.append(" from (");
				sql.append(" select stat_date,sum(pv) as pv , sum(cash/100) as cash,SUM(click) as click ,SUM(disp) as disp  ");
				sql.append(" from t_"+incomeType+"_city_cate_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" and  source1 in('站外其他','SEO','站外引荐','sou','other','直接访问','其他','站内','付费浏览器','广告系列','58联盟','付费其他','SEM')   ");
				sql.append(" group by stat_date  ");
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				sqlMap.put(sql.toString(), cols1);
				
			}
			
		}
		
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("statDate");
	gbList.add("busiLine");
	gbList.add("type");
	
	List<BWRLineSPIncome> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineSPIncome.class, gbList);
	Map<String,List<BWRLineSPIncome>>  rsMap = new HashMap<String, List<BWRLineSPIncome>>();
	
	//总计MAP,不放入返回结果集仅用于计算占比
	Map<String,BWRLineSPIncome> allCountMap = new HashMap<String, BWRLineSPIncome>();
	
	for (BWRLineSPIncome vo : rsList) {
		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		String source = vo.getType();
		if("all".equals(source)){//all 总计VO只放入allCountMap
			allCountMap.put(vo.getStatDate()+vo.getBusiLine(), vo);
			continue;
		}else if("zwff".equals(source)){
			vo.setType("站外付费");
		}else if("zwqt".equals(source)){
			vo.setType("站外其他");
		}else if("zhannei".equals(source)){
			vo.setType("站内");
		}
		
		List<BWRLineSPIncome> tempList = new ArrayList<BWRLineSPIncome>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
	}
	
	//计算占比
	for(Iterator<String> it = rsMap.keySet().iterator();it.hasNext();){
		String busiLine = it.next();
		List<BWRLineSPIncome> tempList = rsMap.get(busiLine);
		for (BWRLineSPIncome vo : tempList) {
			String key = vo.getStatDate()+vo.getBusiLine();
			if(allCountMap.containsKey(key)){
				BWRLineSPIncome allVo = allCountMap.get(key);
				vo.setJzPvZb((double) vo.getJzPv() / allVo.getJzPv());
				vo.setJzCashZb((double) vo.getJzCash() / allVo.getJzCash());
			
				vo.setZdPvZb((double) vo.getZdPv() / allVo.getZdPv());
				vo.setZdCashZb((double) vo.getZdCash() / allVo.getZdCash());
			}
		}
	}
	return rsMap;


}

@Override
	public Map<String, List<BWRLineCusTran>> BMRLineCusTranDatas(
		List<String> dateList) throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	List<String> incomTypeList = new ArrayList<String>();
	incomTypeList.add("jingzhun");
	incomTypeList.add("zhiding");
	incomTypeList.add("zhineng");
	
	for(String searchDate : dateList){
		
		String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
		String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			if("all".equals(busiName)){ //总体 PASS
				continue;
			}
			
			for(String incomeType : incomTypeList){
				
	//			if( !searchDate.equals(dateList.get(0)) || !"huangye".equals(busiName)||!"zhiding".equals(incomeType) ){ 
	//				continue;
	//			}
				/**展现客户数 消耗客户数 消耗ARPU*/
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
				sql.append(" count(distinct case when disp>0 then user_id end) as '有展现客户数', ");
				sql.append(" count(distinct case when cost>0 then user_id end) as '有消耗客户数', ");
				sql.append(" sum(cost)/count(distinct case when cost>0 then user_id end)/100 as '有消耗客户ARPU' ");
				sql.append(" from (");
				sql.append(" select user_id,disp,cost  ");
				sql.append(" from t_"+incomeType+"_user_stat_new_"  + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");		
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("incomeType");
				cols1.add("showCus");
				cols1.add("costCus");
				cols1.add("costARPU");
				
				sqlMap.put(sql.toString(), cols1);
				
				
				/**新增客户数及ARPU*/
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
				sql.append(" count(distinct a.user_id),");
				sql.append(" sum(a.xf)/count(distinct a.user_id)/100 as 'ARPU' ");
				sql.append(" from ");
				sql.append(" ( ");
				//本月消费客户
				sql.append("   select user_id,sum(cost) as xf ");
				sql.append("   from (");
				sql.append("	select user_id,cost ");
				sql.append(" 	from t_"+incomeType+"_user_stat_new_"  + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				sql.append(" 	and business_type_biz = '"+busiName+"' ");
				sql.append("    and cost>0  ");
				
				sql.append("    ) c");
				sql.append(" group by user_id ");
				sql.append(" )a ");
				sql.append(" left join  ");
				sql.append(" ( ");
				
				//上月消费客户
					
				String lastmonthfirstday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",1);//查询日期（上月第一天）
				String lastmonthlastday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",1);//查询日期（上月最后一天）
							
				String yyyyMM1 = lastmonthfirstday.substring(0,lastmonthfirstday.lastIndexOf("-")).replace("-", "");
				
				sql.append(" select user_id  ");
				sql.append(" from  ");
				sql.append(" ( ");
				sql.append(" 	select user_id ");
				sql.append(" 	from t_"+incomeType+"_user_stat_new_"  + yyyyMM1);
				sql.append(" where stat_date >= '"+lastmonthfirstday+ "' ");
				sql.append(" and stat_date <= '"+lastmonthlastday+ "' ");	
				sql.append(" 	and business_type_biz = '"+busiName+"' ");
				sql.append("	and cost>0 ");
				
				sql.append("	)d");
				sql.append(" group by user_id ");
				sql.append(" )b ");
				sql.append(" on a.user_id=b.user_id ");
				sql.append(" where b.user_id is null ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("busiLine");
				cols2.add("incomeType");
				cols2.add("addCus");
				cols2.add("addARPU");
				
				sqlMap.put(sql.toString(), cols2);
				
				/*流失客户数及ARPU*/
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
				sql.append(" count(distinct a.user_id),");
				sql.append(" sum(a.xf)/count(distinct a.user_id)/100 as 'ARPU' ");
				sql.append(" from ");
				sql.append(" ( ");
				//上月消费客户
				sql.append("   select user_id,sum(cost) as xf ");
				sql.append("   from (");
				sql.append("	select user_id,cost ");
				sql.append(" 	from t_"+incomeType+"_user_stat_new_" + yyyyMM1);
				sql.append(" where stat_date >= '"+lastmonthfirstday+ "' ");
				sql.append(" and stat_date <= '"+lastmonthlastday+ "' ");	
				sql.append(" 	and business_type_biz = '"+busiName+"' ");
				sql.append("    and cost>0  ");
				sql.append("    ) c");
				sql.append(" group by user_id ");
				sql.append(" )a ");
				sql.append(" left join  ");
				sql.append(" ( ");
				
				//本月消费客户
				sql.append(" select user_id  ");
				sql.append(" from  ");
				sql.append(" ( ");
				sql.append(" 	select user_id ");
				sql.append(" 	from t_"+incomeType+"_user_stat_new_"  + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				sql.append(" 	and business_type_biz = '"+busiName+"' ");
				sql.append("	and cost>0 ");
				
				sql.append("	)d");
				sql.append(" group by user_id ");
				sql.append(" )b ");
				sql.append(" on a.user_id=b.user_id ");
				sql.append(" where b.user_id is null ");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols3 = new ArrayList<String>();
				cols3.add("statDate");
				cols3.add("busiLine");
				cols3.add("incomeType");
				cols3.add("lostCus");
				cols3.add("lostARPU");
				
				sqlMap.put(sql.toString(), cols3);
				
				/** 处理 TO精准  &&  TO置顶 
				 *  精准TO精准= 上周做精准，本周继续做精准/上周精准消费客户数
				 *  精准TO置顶= 上周做精准，本周只做置顶/上周精准消费客户数
				 *  其他2个以此类推
				 */
				
				if("zhineng".equals(incomeType)){
					continue;
				}
				
				/** 上月消费客户数 */
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
				sql.append(" count(distinct case when cost>0 then user_id end) as '上周消费客户数' ");
				sql.append(" from (");
				sql.append(" select user_id,cost  ");
				sql.append(" from t_"+incomeType+"_user_stat_new_" + yyyyMM1);
				sql.append(" where stat_date >= '"+lastmonthfirstday+ "' ");
				sql.append(" and stat_date <= '"+lastmonthlastday+ "' ");	
				sql.append(" and business_type_biz = '"+busiName+"' ");
				sql.append(" ) a");
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols4 = new ArrayList<String>();
				cols4.add("statDate");
				cols4.add("busiLine");
				cols4.add("incomeType");
				cols4.add("lastWeekCostCus");
				
				sqlMap.put(sql.toString(), cols4);
				
				
				/** 自己 TO 自己  = like  精准 TO 精准   、 置顶 TO 置顶 */
				
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
				sql.append(" count(distinct a.user_id) ");
				sql.append(" from (");
				sql.append("	select distinct user_id from  ");
				sql.append("	(");
				sql.append("		select user_id ");
				sql.append(" 		from t_"+incomeType+"_user_stat_new_" + yyyyMM1);
				sql.append(" where stat_date >= '"+lastmonthfirstday+ "' ");
				sql.append(" and stat_date <= '"+lastmonthlastday+ "' ");	
				sql.append("		and business_type_biz = '"+busiName+"' ");
				sql.append("		and cost>0");
				sql.append("	)c ");
				sql.append(" )a ");
				sql.append(" JOIN ");
				sql.append(" (");
				sql.append("	select distinct user_id from  ");
				sql.append("	( ");
				sql.append("		select user_id  ");
				sql.append(" 		from t_"+incomeType+"_user_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				sql.append("		and business_type_biz = '"+busiName+"' ");
				sql.append("		and cost>0  ");
				sql.append("	)d ");
				sql.append(")b ");
				sql.append("ON a.user_id=b.user_id ");
				
				
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols5 = new ArrayList<String>();
				cols5.add("statDate");
				cols5.add("busiLine");
				cols5.add("incomeType");
				if("jingzhun".equals(incomeType)){
					cols5.add("toJZ");
				}else if("zhiding".equals(incomeType)){
					cols5.add("toZD");
				}
				
				sqlMap.put(sql.toString(), cols5);
				
				
				
				/** 精准 TO 置顶 、 置顶 TO 精准 */
				
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"','" + incomeType+"',");
				sql.append(" count(distinct a.user_id)  ");
				sql.append(" FROM ");
				sql.append(" ( ");
				sql.append("	select distinct user_id from  ");
				sql.append("	( ");
				sql.append("		select user_id");
				sql.append(" 		from t_"+incomeType+"_user_stat_new_" + yyyyMM1);
				sql.append(" where stat_date >= '"+lastmonthfirstday+ "' ");
				sql.append(" and stat_date <= '"+lastmonthlastday+ "' ");	
				sql.append("		and business_type_biz = '"+busiName+"' ");
				sql.append("		and cost>0 ");				
				
				sql.append("	)d ");
				sql.append(" )a");
				sql.append(" JOIN ");
				sql.append(" ( ");
				sql.append("	select distinct user_id from  ");
				sql.append("	( ");
				sql.append("		select user_id ");
				if("jingzhun".equals(incomeType)){
					sql.append(" 		from t_zhiding_user_stat_new_" + yyyyMM);
				}else if("zhiding".equals(incomeType)){
					sql.append(" 		from t_jingzhun_user_stat_new_" + yyyyMM);
				}
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				sql.append("		and cost>0 ");
				
				sql.append("	)e ");
				sql.append(" )b ");
				sql.append(" on a.user_id=b.user_id ");
				sql.append(" LEFT JOIN ");
				sql.append(" (");
				sql.append("	select distinct user_id from  ");
				sql.append("	( ");
				sql.append("		select user_id ");
				sql.append(" 		from t_"+incomeType+"_user_stat_new_" + yyyyMM);
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				sql.append("		and business_type_biz = '"+busiName+"' ");
				sql.append("		and cost>0 ");

				sql.append("	)f ");
				sql.append(" )c");
				sql.append(" on a.user_id=c.user_id ");
				sql.append(" where c.user_id is null; ");
				
				
				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				List<String> cols6 = new ArrayList<String>();
				cols6.add("statDate");
				cols6.add("busiLine");
				cols6.add("incomeType");
				if("jingzhun".equals(incomeType)){
					cols6.add("toZD");
				}else if("zhiding".equals(incomeType)){
					cols6.add("toJZ");
				}
				
				sqlMap.put(sql.toString(), cols6);
			}
			
		}
		
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("statDate");
	gbList.add("busiLine");
	gbList.add("incomeType");
	
	List<BWRLineCusTran> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BWRLineCusTran.class, gbList);
	Map<String,List<BWRLineCusTran>>  rsMap = new HashMap<String, List<BWRLineCusTran>>();
	
	for (BWRLineCusTran vo : rsList) {
		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		
		//计算TO精准  TO置顶的占比 和流失占比
		String incomeType = vo.getIncomeType();
		if("jingzhun".equals(incomeType)){
			vo.setToJZ((double)vo.getToJZ() / vo.getLastWeekCostCus());
			vo.setToZD((double)vo.getToZD() / vo.getLastWeekCostCus());
			vo.setLostZB((double) (1- vo.getToJZ() - vo.getToZD()));
			vo.setIncomeType("精准");
		}else if("zhiding".equals(incomeType)){
			vo.setToJZ((double)vo.getToJZ() / vo.getLastWeekCostCus());
			vo.setToZD((double)vo.getToZD() / vo.getLastWeekCostCus());
			vo.setLostZB((double) (1- vo.getToJZ() - vo.getToZD()));
			vo.setIncomeType("置顶");
		}else if("zhineng".equals(incomeType)){
			vo.setIncomeType("智能");
		}
		
		List<BWRLineCusTran> tempList = new ArrayList<BWRLineCusTran>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
	}
	
	return rsMap;
}


@Override
	public Map<String, List<BMRNaturalFluxData>> BMRNaturalFlux (
		List<String> dateList) throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	List<String> platformTypeList = new ArrayList<String>();
	platformTypeList.add("pc");
	platformTypeList.add("m");
	platformTypeList.add("app");
		
	for(String searchDate : dateList){
		
		String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
		String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
		String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			if("all".equals(busiName)){ //总体 PASS
				continue;
			}		
						
			for(String platformType : platformTypeList){
	
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','" + busiName + "', '" + platformType + "',"); 
				sql.append(" sum(case when page_type='all' then pv end)/count(distinct stat_date) as pv_pc, ");
				sql.append(" sum(case when page_type='all' then uv end)/count(distinct stat_date) as uv_pc, ");
				sql.append(" sum(case when page_type='detail' then pv end)/count(distinct stat_date) as vppv_pc, ");
				sql.append(" sum(case when page_type='detail' then pv end)/sum(case when page_type='all' then uv end) as vppv_uv_pc,");
				sql.append(" sum(case when page_type='list' then pv end)/count(distinct stat_date) as pv_list_pc ");
				sql.append(" from t_netflow_"+platformType+"_pv_" + yyyyMM);
				
				sql.append(" where business_type='"+busiName+"'");
				if(busiName=="zhaopin")
				{
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') ");
					if(platformType=="app")
						sql.append(" and disp_cate2_name='all' ");					
				}
				else sql.append(" and disp_cate1_name='all' ");
				
				if(platformType!="app")
					sql.append(" and source1='all' ");
					
				sql.append(" and disp_city1_name='all' ");
				sql.append(" and stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");
					
				sql.append(" :"+ DBType.db_94_data_app.getValue());
	
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("type");
				cols1.add("Pv");
				cols1.add("Uv");
				cols1.add("Vppv");
				cols1.add("Rate");
				cols1.add("ListPv");
				
				
				sqlMap.put(sql.toString(), cols1);
				
			}
		}
	
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("statDate");
	gbList.add("busiLine");
	gbList.add("type");
	
	List<BMRNaturalFluxData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BMRNaturalFluxData .class, gbList);
	Map<String,List<BMRNaturalFluxData>>  rsMap = new HashMap<String, List<BMRNaturalFluxData>>(); 
	

	//加入总计 并区分业务线
	for (BMRNaturalFluxData vo : rsList) {
		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		List<BMRNaturalFluxData> tempList = new ArrayList<BMRNaturalFluxData>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
	}

	for(Iterator<String> it = rsMap.keySet().iterator();it.hasNext();){
		String busiLine = it.next();
		List<BMRNaturalFluxData> tempList = rsMap.get(busiLine);
		
		for(String searchDate : dateList){
			
			BMRNaturalFluxData allVo=new BMRNaturalFluxData();
							
			for (BMRNaturalFluxData vo : tempList) {
				String key = vo.getStatDate()+vo.getBusiLine();
				
				String nowkey = searchDate+busiLine;
				if(key.equals(nowkey)){
					
					allVo.setStatDate(vo.getStatDate());
					allVo.setBusiLine(busiLine);
					allVo.setType("总体");
					
					allVo.setPv(allVo.getPv()+vo.getPv());
					allVo.setUv(allVo.getUv()+vo.getUv());
					allVo.setListPv(allVo.getListPv()+vo.getListPv());
					allVo.setVppv(allVo.getVppv()+vo.getVppv());
					allVo.setRate((double)(allVo.getVppv())/(double)(allVo.getUv()));				
				}
			}
		
		
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
			
			tempList.add(allVo);
			rsMap.put(busiLine, tempList);
		}
	}
	return rsMap;
}

@Override
	public List<BMRDemandAnalysisData>  BMRDemandAnalysis (
			List<String> dateList)throws Exception {
	
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
		int flag=0;
		for(String searchDate : dateList){
		
			flag++;

			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
		
			String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			
			for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
				String busiName = it.next();
				
				if("all".equals(busiName)){ //总体 PASS
					continue;
				}
		
				//商家需求
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(info_valid_add_count)/count(distinct stat_date) ");
				sql.append(" FROM t_datainfo_stat_new_" + yyyyMM );
				sql.append(" where business_name= '"+busiName+"' and belong_city1_id='all'  ");
				sql.append(" and belong_cate1_id='all' and belong_cate2_id='all'  ");
				sql.append(" and source='all' and platform='all' and user_type='all'  ");
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("busiLine");
				cols1.add("BusDemand");		
				sqlMap.put(sql.toString(), cols1);
				
				//用户需求
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.uv)/count(distinct stat_date) as '总UV' ");
				sql.append(" FROM ( " );
				
				sql.append(" select stat_date,sum(uv) as uv ");		
				sql.append(" FROM t_netflow_pc_city_cate_pv_" + yyyyMM );
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				
				sql.append(" and business_type= '"+busiName+"' and page_type='all'  ");
				sql.append(" and disp_city1_name='all' ");
						
				if(busiName=="zhaopin")
				{
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') and disp_cate2_name='all'  ");
				}
				else
					sql.append(" and disp_cate1_name='all' ");
		
				sql.append(" group by stat_date ");
				sql.append(" union all ");
				
				sql.append(" select stat_date,sum(uv) as uv ");		
				sql.append(" FROM t_netflow_m_city_cate_pv_" + yyyyMM );			
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				
				sql.append(" and business_type= '"+busiName+"' and page_type='all'  ");
				sql.append(" and disp_city1_name='all' ");
				
				if(busiName=="zhaopin")
				{
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') and disp_cate2_name='all'  ");
				}
				else
					sql.append(" and disp_cate1_name='all' ");
				
				
				sql.append(" group by stat_date ");
				sql.append(" union all ");
				
				sql.append(" select stat_date,sum(uv) as uv ");		
				sql.append(" FROM t_netflow_app_pv_" + yyyyMM );				
				sql.append(" where stat_date >= '"+beginday+ "' ");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				
				sql.append(" and business_type= '"+busiName+"' and page_type='all' ");
				sql.append(" and disp_city1_name='all' ");
				
				if(busiName=="zhaopin")
				{
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') and disp_cate2_name='all'  ");
				}
				else
					sql.append(" and disp_cate1_name='all' ");
								
				sql.append(" group by stat_date ");
				sql.append(" )a ");				
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("busiLine");
				cols2.add("CusDemand");		
				sqlMap.put(sql.toString(), cols2);
			
				if(flag == 3)//如果是去年的 就不算了算今天的吧 这里不需要同比
				{
					searchDate=dateList.get(0);
					yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
				}
				//精准人均获得点击
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.rjdj)/count(distinct stat_date) ");
				sql.append(" FROM (" );			
				sql.append(" select stat_date,sum(click)/count(distinct case when cost>0 then user_id end) rjdj ");
				sql.append(" FROM t_jingzhun_user_stat_new_" + yyyyMM );
				sql.append(" where business_type_biz= '"+busiName+"' ");
				sql.append(" group by 1)a ");		
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				List<String> cols3 = new ArrayList<String>();
				cols3.add("statDate");
				cols3.add("busiLine");
				cols3.add("JingzhunClickPer");		
				sqlMap.put(sql.toString(), cols3);
				
				//置顶人均获得点击
				sql.delete(0, sql.length()+1);
				sql.append(" select '" + searchDate + "','"+busiName+"',");
				sql.append(" sum(a.rjdj)/count(distinct stat_date) ");
				sql.append(" FROM (" );
				
				sql.append(" select stat_date,sum(click)/count(distinct case when cost>0 then user_id end) rjdj ");
				sql.append(" FROM t_zhiding_user_stat_new_" + yyyyMM );
				sql.append(" where business_type_biz= '"+busiName+"' ");
				sql.append(" group by 1)a ");		
				sql.append(" :"+ DBType.db_94_data_app.getValue());
				
				List<String> cols4 = new ArrayList<String>();
				cols4.add("statDate");
				cols4.add("busiLine");
				cols4.add("ZhidingClickPer");		
				sqlMap.put(sql.toString(), cols4);
				
				if(flag == 3)
				{
					searchDate=dateList.get(2);//换回来
					yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("busiLine");
	
		List<BMRDemandAnalysisData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, BMRDemandAnalysisData.class, gbList);
	
		for (BMRDemandAnalysisData vo : rsList) {
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
		}
		
		return rsList;
	}


@Override
	public Map<String, List<onlineProductIncome>> getOnlineProductIncome (
		String searchDate) throws Exception {

	Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
	StringBuffer sql = new StringBuffer();
	
	HashMap<String,String>  busiLineConsMap = getBusiLineConsMap();
	
	//从一月算到当前月
	String yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
	String tt="201601";	
	while(yyyyMM.compareTo(tt)>=0)
	{
	
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
			String busiName = it.next();
			
			//if(  !"huangye".equals(busiName) )continue;
					
			//精准			
			sql.delete(0, sql.length()+1);
			sql.append(" select '" + yyyyMM + "','" + busiName + "', "); 
			sql.append(" round(sum(cash)/count(distinct stat_date)/1000000,5)");
			sql.append(" FROM t_jingzhun_user_stat_new_" + yyyyMM );	
			if(busiName!="all")
			{
				sql.append(" where business_type_biz='"+busiName+"'");
			}			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
	
			List<String> cols1 = new ArrayList<String>();
			cols1.add("time");
			cols1.add("busiLine");
			cols1.add("jzCash");
			sqlMap.put(sql.toString(), cols1);
			
			//置顶
			sql.delete(0, sql.length()+1);
			sql.append(" select '" + yyyyMM + "','" + busiName + "', "); 
			sql.append(" round(sum(cash)/count(distinct stat_date)/1000000,5)");
			sql.append(" FROM t_zhiding_user_stat_belong_" + yyyyMM );
			
			if(busiName!="all")
			{
				sql.append(" where business_type_biz='"+busiName+"'");
			}			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
	
			List<String> cols2 = new ArrayList<String>();
			cols2.add("time");
			cols2.add("busiLine");
			cols2.add("zdCash");
			sqlMap.put(sql.toString(), cols2);
		
			//智能
			sql.delete(0, sql.length()+1);
			sql.append(" select '" + yyyyMM + "','" + busiName + "', "); 
			sql.append(" round(sum(cash)/count(distinct stat_date)/1000000,5)");
			sql.append(" FROM t_zhineng_user_stat_new_" + yyyyMM );
			
			if(busiName!="all")
			{
				sql.append(" where business_type_biz='"+busiName+"'");
			}			
			sql.append(" :"+ DBType.db_94_data_app.getValue());
	
			List<String> cols3 = new ArrayList<String>();
			cols3.add("time");
			cols3.add("busiLine");
			cols3.add("znCash");
			sqlMap.put(sql.toString(), cols3);
			
		}
		int t = Integer.parseInt(yyyyMM);
		t--;	
		yyyyMM = String.valueOf(t); 	
	}
	
	List<String> gbList = new ArrayList<String>();
	gbList.add("time");
	gbList.add("busiLine");
	
	List<onlineProductIncome> rsList = comDeal.AssembledDataBySqlMap(sqlMap, onlineProductIncome .class, gbList);
	Map<String,List<onlineProductIncome>>  rsMap = new HashMap<String, List<onlineProductIncome>>(); 
	
	double[] all_jz={229.05,115.23,72.03,199.72,209.16,233,258.36,278.92,246.9,247.82,285.7,296.86,304.96} ;
	double[] all_zd={131.44,53.57,31.71,114.89,116.78,122.84,137.44,147.89,157.08,174.35,170.67,178.43,171.65} ;
	double[] all_zn={11.39,8.29,4.55,11.39,11.74,12.2,11.65,11.09,11.95,11.46,13.93,14.58,13.81};

	double[] huangye_jz={100.81,54.04,32.7,77.99,93.41,105.2,114.72,127.63,110.2,105.91,118.05,130.21,139.68} ;
	double[] huangye_zd={29.24,12.22,7.37,19.12,23.37,26.18,29.36,33.6,35.7,40.38,38.26,42.42,42.85};
	double[] huangye_zn={2.02,1.47,0.92,1.54,1.85,1.88,1.85,1.92,1.77,1.67,2.57,3.34,3.47};

	double[] zhaopin_jz={56.12,16.3,12.85,59.55,49.72,56.42,66.68,67.31,63.36,70.74,78.63,73.84,58.09};
	double[] zhaopin_zd={40.28,10.28,7.15,46.27,38.49,40.56,46.03,49.35,53.4,54.61,49.34,47.56,40.31};
	double[] zhaopin_zn={3.4,2.01,1,3.9,3.69,3.83,3.72,3.59,4.16,3.36,3.63,3.87,4};

	double[] fangchan_jz={25.93,11.66,7.31,17.06,18.51,22.66,25.84,30.04,27.05,29.16,37.99,39.87,44};
	double[] fangchan_zd={47.75,22.83,11.85,39.1,41.42,42.22,44.57,48.95,52.46,63.62,67.02,69.66,69.27};
	double[] fangchan_zn={3.02,1.99,1.24,2.77,2.89,2.97,3.54,3.52,3.46,3.38,3.95,3.5,3.03};

	double[] ershou_jz={35.55,24.44,14.21,36.47,39.29,42.09,42.53,36.59,33.79,33.46,39.1,40.1,44.58};
	double[] ershou_zd={6.12,3.77,2.24,4.4,5.83,6.62,7.62,6.92,6.6,7.18,7.06,7.46,7.72};
	double[] ershou_zn={2.01 ,1.95 ,0.98 ,2.24 ,2.39 ,2.68 ,1.88 ,1.32 ,1.54 ,1.74 ,2.36 ,2.66 ,2.4};

	double[] ershouche_jz={8.8,6.89,4.97,8.65,8.23,6.63,8.59,10.44,8.94,6.84,8.95,10.9,15.6};
	double[] ershouche_zd={7.73, 4.45, 3.09, 5.78, 7.43, 7.02, 9.52, 8.57, 8.45, 8.09, 8.48, 10.83, 11.07};
	double[] ershouche_zn={0.93,0.83,0.38,0.92,0.92,0.84,0.67,0.74,1.01,1.32,1.42,1.21,0.91};

	//算同比
	for (onlineProductIncome vo : rsList) {
		
		String mm = vo.getTime().substring(4,6);
		int t = Integer.parseInt(mm);
		t--;
		
		if(vo.getBusiLine().equals("all"))
		{
			vo.setJzRate((double)((vo.getJzCash()/all_jz[t]-1)));
			vo.setZdRate((double)((vo.getZdCash()/all_zd[t]-1)));
			vo.setZnRate((double)((vo.getZnCash()/all_zn[t]-1)));		
			vo.setJzCash_lastyear((double)((all_jz[t])));
			vo.setZdCash_lastyear((double)((all_zd[t])));
			vo.setZnCash_lastyear((double)((all_zn[t])));
		}
		else if(vo.getBusiLine().equals("huangye"))
		{
			vo.setJzRate((double)((vo.getJzCash()/huangye_jz[t]-1)));
			vo.setZdRate((double)((vo.getZdCash()/huangye_zd[t]-1)));
			vo.setZnRate((double)((vo.getZnCash()/huangye_zn[t]-1)));
			vo.setJzCash_lastyear((double)((huangye_jz[t])));
			vo.setZdCash_lastyear((double)((huangye_zd[t])));
			vo.setZnCash_lastyear((double)((huangye_zn[t])));
		}
		else if(vo.getBusiLine().equals("zhaopin"))
		{
			vo.setJzRate((double)((vo.getJzCash()/zhaopin_jz[t]-1)));
			vo.setZdRate((double)((vo.getZdCash()/zhaopin_zd[t]-1)));
			vo.setZnRate((double)((vo.getZnCash()/zhaopin_zn[t]-1)));
			vo.setJzCash_lastyear((double)((zhaopin_jz[t])));
			vo.setZdCash_lastyear((double)((zhaopin_zd[t])));
			vo.setZnCash_lastyear((double)((zhaopin_zn[t])));
		}
		else if(vo.getBusiLine().equals("fangchan"))
		{
			vo.setJzRate((double)((vo.getJzCash()/fangchan_jz[t]-1)));
			vo.setZdRate((double)((vo.getZdCash()/fangchan_zd[t]-1)));
			vo.setZnRate((double)((vo.getZnCash()/fangchan_zn[t]-1)));
			vo.setJzCash_lastyear((double)((fangchan_jz[t])));
			vo.setZdCash_lastyear((double)((fangchan_zd[t])));
			vo.setZnCash_lastyear((double)((fangchan_zn[t])));
		}
		else if(vo.getBusiLine().equals("ershou"))
		{
			vo.setJzRate((double)((vo.getJzCash()/ershou_jz[t]-1)));
			vo.setZdRate((double)((vo.getZdCash()/ershou_zd[t]-1)));
			vo.setZnRate((double)((vo.getZnCash()/ershou_zn[t]-1)));
			vo.setJzCash_lastyear((double)((ershou_jz[t])));
			vo.setZdCash_lastyear((double)((ershou_zd[t])));
			vo.setZnCash_lastyear((double)((ershou_zn[t])));
		}
		else if(vo.getBusiLine().equals("ershouche"))
		{
			vo.setJzRate((double)((vo.getJzCash()/ershouche_jz[t]-1)));
			vo.setZdRate((double)((vo.getZdCash()/ershouche_zd[t]-1)));
			vo.setZnRate((double)((vo.getZnCash()/ershouche_zn[t]-1)));
			vo.setJzCash_lastyear((double)((ershouche_jz[t])));
			vo.setZdCash_lastyear((double)((ershouche_zd[t])));
			vo.setZnCash_lastyear((double)((ershouche_zn[t])));
		}

		String busiLine = vo.getBusiLine();
		busiLine = MyStore.busilineTran(vo.getBusiLine());
		vo.setBusiLine(busiLine);
		
		List<onlineProductIncome> tempList = new ArrayList<onlineProductIncome>();
		if(rsMap.containsKey(busiLine)){
			tempList = rsMap.get(busiLine);
		}
		
		tempList.add(vo);
		rsMap.put(busiLine, tempList);
		
	}
	//去年多出来的那几个月。。。
	//从当前月的下一个月到12月
	yyyyMM = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
	int t1 = Integer.parseInt(yyyyMM);
	t1++;	
	yyyyMM = String.valueOf(t1); 
	tt="201612";	
	while(yyyyMM.compareTo(tt)<=0)
	{
	
		String mm = yyyyMM.substring(4,6);
		int t = Integer.parseInt(mm);
		t--;
		
		for(Iterator<String> it = busiLineConsMap.keySet().iterator(); it.hasNext();){
						
			String busiName = it.next();
			
			//if(  !"huangye".equals(busiName) )continue;
			
			onlineProductIncome vo =new onlineProductIncome();
			vo.setTime(yyyyMM);
			
			if(busiName=="all")
			{
				vo.setJzCash_lastyear((double)((all_jz[t])));
				vo.setZdCash_lastyear((double)((all_zd[t])));
				vo.setZnCash_lastyear((double)((all_zn[t])));
				vo.setBusiLine(busiName);
			}
			else if(busiName=="huangye")
			{
			
				vo.setJzCash_lastyear((double)((huangye_jz[t])));
				vo.setZdCash_lastyear((double)((huangye_zd[t])));
				vo.setZnCash_lastyear((double)((huangye_zn[t])));
				vo.setBusiLine(busiName);
			}
			else if(busiName=="zhaopin")
			{
			
			vo.setJzCash_lastyear((double)((zhaopin_jz[t])));
			vo.setZdCash_lastyear((double)((zhaopin_zd[t])));
			vo.setZnCash_lastyear((double)((zhaopin_zn[t])));
			vo.setBusiLine(busiName);
			}
			else if(busiName=="fangchan")
			{
				
				vo.setJzCash_lastyear((double)((fangchan_jz[t])));
				vo.setZdCash_lastyear((double)((fangchan_zd[t])));
				vo.setZnCash_lastyear((double)((fangchan_zn[t])));
				vo.setBusiLine(busiName);
			}
			else if(busiName=="ershou")
			{
				
				vo.setJzCash_lastyear((double)((ershou_jz[t])));
				vo.setZdCash_lastyear((double)((ershou_zd[t])));
				vo.setZnCash_lastyear((double)((ershou_zn[t])));
				vo.setBusiLine(busiName);
			}
			else if(busiName=="ershouche")
			{
			
				vo.setJzCash_lastyear((double)((ershouche_jz[t])));
				vo.setZdCash_lastyear((double)((ershouche_zd[t])));
				vo.setZnCash_lastyear((double)((ershouche_zn[t])));
				vo.setBusiLine(busiName);
			}
		
			String busiLine = vo.getBusiLine();
			busiLine = MyStore.busilineTran(vo.getBusiLine());
			vo.setBusiLine(busiLine);
			
			List<onlineProductIncome> tempList = new ArrayList<onlineProductIncome>();
			if(rsMap.containsKey(busiLine)){
				tempList = rsMap.get(busiLine);
			}
		
			tempList.add(vo);
			rsMap.put(busiLine, tempList);	
		}
		
		int t2=Integer.parseInt(yyyyMM)+1;
		yyyyMM = String.valueOf(t2); 
	}
	
	return rsMap;
}
}
